﻿CREATE TABLE esf_sso.Person (
  ID                   int IDENTITY NOT NULL, 
  FirstName            varchar(40) NOT NULL, 
  SecondName           varchar(40) NULL, 
  SurName              varchar(40) NOT NULL, 
  LastName             varchar(40) NULL, 
  BirthDate            datetime NULL, 
  GenderID             int NULL, 
  IdentificationTypeID int NOT NULL, 
  IdentificationNumber varchar(20) NOT NULL, 
  Email                varchar(160) NOT NULL, 
  EnterpriseID         int NULL, 
  OtherEnterprise      varchar(60) NULL, 
  Profession           varchar(200) NULL, 
  Position             varchar(200) NULL, 
  PRIMARY KEY (ID), 
  CONSTRAINT U_Person_Email 
    UNIQUE (Email), 
  CONSTRAINT U_Person_Identification 
    UNIQUE (IdentificationTypeID, IdentificationNumber));
CREATE TABLE esf_sso.BasicReference (
  ID                   int IDENTITY NOT NULL, 
  BasicReferenceTypeID int NOT NULL, 
  Code                 varchar(500) NULL, 
  Name                 varchar(500) NOT NULL, 
  Description          varchar(1000) NULL, 
  IsEnabled            bit NOT NULL, 
  Extra_I              varchar(500) NULL, 
  Extra_II             varchar(500) NULL, 
  Extra_III            varchar(500) NULL, 
  CreationDateTime     datetime NULL, 
  LastUpdateDateTime   datetime NULL, 
  UserCreationAlias    varchar(20) NULL, 
  UserLastUpdateAlias  varchar(20) NULL, 
  PRIMARY KEY (ID), 
  CONSTRAINT U_BasRef
    UNIQUE (BasicReferenceTypeID, Name, Code), 
  CONSTRAINT U_BasRef_Code 
    UNIQUE (BasicReferenceTypeID, Code));
CREATE TABLE esf_sso.BasicReferenceType (
  ID   int IDENTITY NOT NULL, 
  Name varchar(150) NOT NULL UNIQUE, 
  PRIMARY KEY (ID));
CREATE TABLE esf_sso.[User] (
  PersonID               int NOT NULL, 
  Alias                  varchar(40) NOT NULL, 
  Password               varchar(160) NOT NULL, 
  IsEnabled              bit NOT NULL, 
  SecurityQuestionID     int NOT NULL, 
  SecurityQuestionAnswer varchar(60) NOT NULL, 
  CreationDateTime       datetime NOT NULL, 
  LastUpdateDateTime     datetime NULL, 
  UserCreationAlias      varchar(20) NOT NULL, 
  UserLastUpdateAlias    varchar(20) NULL, 
  PRIMARY KEY (PersonID), 
  CONSTRAINT U_User_Alias 
    UNIQUE (Alias));
CREATE TABLE esf_sso.Location (
  ID              int IDENTITY NOT NULL, 
  CountryID       int NULL, 
  CityID          int NULL, 
  CityName        varchar(60) NULL, 
  Name            varchar(100) NOT NULL, 
  PhoneNumber     varchar(30) NULL, 
  CellPhoneNumber varchar(30) NULL, 
  Address         varchar(100) NULL, 
  PRIMARY KEY (ID));
CREATE TABLE esf_sso.Enterprise (
  EnterpriseID              int NOT NULL, 
  IdentificationNumber      varchar(30) NOT NULL, 
  IdentificationTypeID      int NOT NULL, 
  IdentificationNumberDigit varchar(10) NULL, 
  PRIMARY KEY (EnterpriseID), 
  CONSTRAINT U_Enterprise_Identification 
    UNIQUE (IdentificationNumber, IdentificationTypeID));
CREATE TABLE esf_sso.UserSessionLog (
  ID                       uniqueidentifier NOT NULL, 
  UserPersonID             int NOT NULL, 
  StartDateTime            datetime NOT NULL, 
  EndDateTime              datetime NULL, 
  UserHostAddress          varchar(100) NOT NULL, 
  UserMachineIdentificator varchar(60) NULL, 
  PRIMARY KEY (ID));
CREATE TABLE esf_sso.User_BasicReference (
  UserPersonID     int NOT NULL, 
  BasicReferenceID int NOT NULL, 
  CONSTRAINT U_User_BasicReference 
    UNIQUE (BasicReferenceID, UserPersonID));
CREATE TABLE esf_sso.ContactUs (
  ID                varchar(36) NOT NULL, 
  ContactUsTypeID   int NOT NULL, 
  ApplicationID     int NOT NULL, 
  Comments          varchar(4000) NOT NULL, 
  SenderEmail       varchar(150) NOT NULL, 
  Title             varchar(200) NULL, 
  CreationDateTime  datetime NOT NULL, 
  Response          varchar(3000) NULL, 
  ResponseUserAlias varchar(20) NULL, 
  ResponseDateTime  datetime NULL, 
  PRIMARY KEY (ID));
CREATE TABLE esf_sso.TreeBasicReference (
  ID                     int IDENTITY NOT NULL, 
  ParentBasicReferenceID int NOT NULL, 
  BasicReferenceID       int NULL, 
  Value                  varchar(8000) NULL, 
  PRIMARY KEY (ID), 
  CONSTRAINT U_TreeBasicReference 
    UNIQUE (BasicReferenceID, ParentBasicReferenceID));
CREATE TABLE esf_sso.Person_Location (
  LocationID int NOT NULL, 
  PersonID   int NOT NULL);
CREATE TABLE esf_files.FileReference (
  ID                   int IDENTITY NOT NULL, 
  Description          varchar(260) NULL, 
  URL                  varchar(4000) NOT NULL, 
  LengthBytes          bigint NULL, 
  FileTypeID           int NULL, 
  CreationDateTime     datetime NULL, 
  LastUpdateDateTime   datetime NULL, 
  LastWriteDateTime    datetime NULL, 
  MonitoredDirectoryID int NULL, 
  ExceptionMessage VARCHAR (4000) NULL,
  CONSTRAINT PK_esf_files_Files 
    PRIMARY KEY (ID), 
  CONSTRAINT U_File_URL 
    UNIQUE (URL));
CREATE TABLE esf_sso.BasicReference_File (
  FileID           int NOT NULL, 
  BasicReferenceID int NOT NULL);
CREATE TABLE esf_sso.BasicReference_Location (
  BasicReferenceID int NOT NULL, 
  LocationID       int NOT NULL);
CREATE TABLE esf_sso.User_BasicReference_Values (
  UserPersonID     int NOT NULL, 
  BasicReferenceID int NOT NULL, 
  Value            varchar(1000) NOT NULL);
CREATE TABLE esf_sso.TreeBasicReferenceValue (
  Value              varchar(8000) NOT NULL, 
  BasicReferenceID_0 int NOT NULL, 
  BasicReferenceID_1 int NOT NULL, 
  BasicReferenceID_2 int NOT NULL);
ALTER TABLE esf_sso.BasicReference ADD CONSTRAINT FK_BasRef_BasRefType_Type FOREIGN KEY (BasicReferenceTypeID) REFERENCES esf_sso.BasicReferenceType (ID);
ALTER TABLE esf_sso.[User] ADD CONSTRAINT FK_User_Person_Person FOREIGN KEY (PersonID) REFERENCES esf_sso.Person (ID);
ALTER TABLE esf_sso.Person ADD CONSTRAINT FK_Per_BasRef_Gender FOREIGN KEY (GenderID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Person ADD CONSTRAINT FK_Per_BasRef_IdentificationType FOREIGN KEY (IdentificationTypeID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Location ADD CONSTRAINT FK_PerLoc_BasRefCity FOREIGN KEY (CityID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Enterprise ADD CONSTRAINT FK_Ent_BasRef_IdentificationType FOREIGN KEY (IdentificationTypeID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Person ADD CONSTRAINT FK_Per_Ent_Enterprise FOREIGN KEY (EnterpriseID) REFERENCES esf_sso.Enterprise (EnterpriseID);
ALTER TABLE esf_sso.UserSessionLog ADD CONSTRAINT FK_USLog_Usr_User FOREIGN KEY (UserPersonID) REFERENCES esf_sso.[User] (PersonID);
ALTER TABLE esf_sso.[User] ADD CONSTRAINT FK_Usr_BasRef_SecurityQuestion FOREIGN KEY (SecurityQuestionID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Enterprise ADD CONSTRAINT FK_Ent_BasRef_Enterprise FOREIGN KEY (EnterpriseID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.User_BasicReference ADD CONSTRAINT FK_User_BasRef_BasRef FOREIGN KEY (BasicReferenceID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.User_BasicReference ADD CONSTRAINT FK_User_BasRef_User FOREIGN KEY (UserPersonID) REFERENCES esf_sso.[User] (PersonID);
ALTER TABLE esf_sso.ContactUs ADD CONSTRAINT FK_ConUs_BasRef_Application FOREIGN KEY (ApplicationID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.TreeBasicReference ADD CONSTRAINT FK_TreeBasRef_BasRef FOREIGN KEY (BasicReferenceID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.TreeBasicReference ADD CONSTRAINT FK_TreeParBasRef_BasRef FOREIGN KEY (ParentBasicReferenceID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Location ADD CONSTRAINT FK_PerLoc_BasRefCountry FOREIGN KEY (CountryID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Person_Location ADD CONSTRAINT FK_Lct_PerLct FOREIGN KEY (LocationID) REFERENCES esf_sso.Location (ID);
ALTER TABLE esf_sso.Person_Location ADD CONSTRAINT FK_Per_PerLct FOREIGN KEY (PersonID) REFERENCES esf_sso.Person (ID);
ALTER TABLE esf_sso.BasicReference_File ADD CONSTRAINT FK_File_BasRefFile FOREIGN KEY (FileID) REFERENCES esf_files.FileReference (ID);
ALTER TABLE esf_sso.BasicReference_File ADD CONSTRAINT FKBasicRefer932173 FOREIGN KEY (BasicReferenceID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.BasicReference_Location ADD CONSTRAINT FK_BasRef_BasRefLoc FOREIGN KEY (BasicReferenceID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.BasicReference_Location ADD CONSTRAINT FK_Loc_BasRefLoc FOREIGN KEY (LocationID) REFERENCES esf_sso.Location (ID);
ALTER TABLE esf_sso.User_BasicReference_Values ADD CONSTRAINT FK_BasRef_BasRefValues FOREIGN KEY (BasicReferenceID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.User_BasicReference_Values ADD CONSTRAINT FK_Usr_BasRefValues FOREIGN KEY (UserPersonID) REFERENCES esf_sso.[User] (PersonID);
ALTER TABLE esf_sso.TreeBasicReferenceValue ADD CONSTRAINT FKTreeBasicR350721 FOREIGN KEY (BasicReferenceID_0) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.TreeBasicReferenceValue ADD CONSTRAINT FKTreeBasicR350722 FOREIGN KEY (BasicReferenceID_1) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.TreeBasicReferenceValue ADD CONSTRAINT FKTreeBasicR350723 FOREIGN KEY (BasicReferenceID_2) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_files.FileReference ADD CONSTRAINT FK_FilRef_BasRef_FileTypeID FOREIGN KEY (FileTypeID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.Enterprise ADD CONSTRAINT FK_Ent_BasRef FOREIGN KEY (EnterpriseID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_files.FileReference ADD CONSTRAINT FK_FilRef_BasRef_MonitoredDirectoryID FOREIGN KEY (MonitoredDirectoryID) REFERENCES esf_sso.BasicReference (ID);
ALTER TABLE esf_sso.ContactUs ADD CONSTRAINT FKContactUs148032 FOREIGN KEY (ContactUsTypeID) REFERENCES esf_sso.BasicReference (ID);
CREATE INDEX IX_BasRef_BasicReferenceTypeID 
  ON esf_sso.BasicReference (BasicReferenceTypeID);
CREATE UNIQUE INDEX IX_BasRef_BasicReferenceTypeID_Name
  ON esf_sso.BasicReference (BasicReferenceTypeID, Name);
CREATE UNIQUE INDEX U_TreeBasicReferenceValue 
  ON esf_sso.TreeBasicReferenceValue (BasicReferenceID_0, BasicReferenceID_1, BasicReferenceID_2);


CREATE TABLE esf_sso.OperationExecution
(
	CustomOperationName VARCHAR (500) NULL,
	XMLParams XML NULL,
	UserAlias VARCHAR (20) NULL,
	ExecutionDateTime DATETIME,
	UserHostAddress VARCHAR (400) NULL
)